![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Reduce Unnecessary Memory Usage One of the best ways to free up memory for Oracle is to free up some memory used by the file system buffers. The file system buffers are used by the operating system to cache data. As you know, there can be a significant performance increase when accessing files that have been cached. But Oracle uses a feature called synchronous I/O to ensure that writes to the disk are not returned until the data has actually been written. Because Oracle must guarantee that I/Os have been written to the disk to ensure data integrity, OS disk write caching cannot be done. By default, the number of file system buffers is determined by the amount of memory in the system. Because Oracle bypasses the disk cache on writes and uses the SGA for reads, you really dont need a large number of file system buffers. By reducing this number, you may see a slight performance decrease with OS operations but any additional memory allocated to the SGA increases Oracle performance. To tune the OS buffer cache, use the following guidelines:
By reducing the unnecessary memory used for disk caching, more memory can be allocated to the SGA. Dont reduce the disk cache so far that it is difficult to run OS commands and access Oracle parameter files. Do not set the disk cache buffers less than 600. These values represent the number of 512 byte blocks. SGA Tuning To maximize performance, allocate as much memory as possible to the SGA. Use the techniques discussed in Chapters 9 and 10 to determine whether memory is best used for the shared pool or for database block buffers. In UNIX, the shared memory area used by Oracle for the SGA is usually contiguous. However, if you have multiple instances of Oracle that have started and stopped several times, the shared memory area may no longer be contiguous. If you use 4M pages, you are guaranteed to have at least 4M of contiguous memory. The amount of memory allocated for shared memory is the product of two OS-tunable parameters: SHMMAX and SHMSEG. SHMMAX specifies the maximum size of a shared memory segment; SHMSEG specifies the maximum number of shared memory segments available in the system. Applications are responsible for allocating only the amount of shared memory they need and so do not waste space. In SCO UNIX and UnixWare, the parameters SHMMAX and SHMSEG are located in the UNIX parameter file /ETC/CONF/CF.D/STUNE. In Solaris, the shared memory parameters are set in the file /ETC/SYSTEM. Your OS administrators manual should have more information on setting system parameters.
To allocate 4M pages for shared memory, use the following parameters in the STUNE file:
The amount of memory allocated to a single user must also be tuned in the UNIX operating system. Because Oracle is treated the same as any other user, you must allocate enough memory for Oracle to use for the SGA and the server processes. The amount of memory available for users is tuned with the following parameters:
Make sure that sufficient memory is available so that Oracle can allocate for the SGA. Remember to save memory for the user processes as well. You should frequently monitor your system to make sure that no paging is occurring at any time.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |